在Excel（XLSX）中聚焦并确认公式之前，不会对java公式单元格进行求值

Question

因此，我使用Apache POI（最新稳定版本5.0.0中的POI ooxml）并打开一个现有的Excel（XSLX）文件进行编辑（它基本上是一个模板文件，用于填充其他数据）。我添加了多个新行数据并再次导出Excel。只要我只添加常规内容单元格，所有操作都可以

现在，我有一列要在其中添加公式单元格，我使用以下代码（在本例中简化了，您可以放心，它通常会编译/运行并在末尾生成一个填充的Excel文件）来执行此操作：

File excelFileToRead = new File(<some filename here>);
InputStream inp = new FileInputStream(excelFileToRead);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row dateRangeRow = sheet.getRow(0);

// fill first cell with some date
Cell cell = row.getCell(0);
if(cell == null) row.createCell(0)
Date someDate = new Date();
cell.setCellValue(someDate);

// add formula to second cell to display the week number
Cell formCell = row.getCell(1);
if(formCell == null) row.createCell(1);
cell.setCellFormula("WEEKNUM(A1)");

// evaluate all formula fields before saving
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

//some routine to save as a file follows here, not exactly relevant here

总的来说，这很好用。第一个单元格将创建并填充今天的日期，第二个单元格也将创建为公式单元格

现在问题来了：当我打开Excel电子表格时，我可以看到数据，在公式单元格中我只看到“#WERT”（使用德语Excel，我假设在英语版本中它会显示类似“#VALUE”的内容）

当我简单地点击Excel中的公式编辑器并再次移除焦点时，它会正确计算公式，并且单元格显示正确的周数

我以前读过的Excel中已有的公式有一些问题，当我向工作表中添加数据时，这些公式没有得到更新，但是通过调用XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);可以解决这些问题 由于某些原因，它不会影响自定义创建的公式单元格

我还尝试在创建后单独评估新创建的公式单元格：

FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.evaluateFormulaCell(formCell);

这在这里也没有产生任何变化

你知道我的代码或方法有什么问题吗

顺便说一下，我使用的是Excel版本16.53（Excel for Mac），但我真的希望它与确切的Excel版本无关：-）

注: 我发现了一个旧线程（在POI 5.0.0发布之前），它似乎讨论了同样的问题，但是使用了旧的POI版本，并且如上所述，我遵循了调用evaluateAllFormulaCells（…）的一般实践在保存之前，甚至在每个公式单元格创建之后调用evaluateFormulaCell（单元格）：Apache POI formulas not evaluating

Answer 1

这是由于在计算WEEKNUM函数时apache poi中出现错误造成的

如果省略了[return_type]，则ist的计算结果总是#VALUE错误。但是，即使您设置了[return_type]，它的计算结果也并不总是正确的

如果执行以下操作，您可以看到：

...
FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = formulaEvaluator.evaluate(formCell);
System.out.println(cellValue);
...

如果A1包含日期9/27/2021且B1包含公式=WEEKNUM(A1)，则apache poi{}将其计算为#VALUE。如果B1包含公式=WEEKNUM(A1,1)，则apache poi{}将其计算为39，但Excel将其计算为40

要解决此错误，可以在打开文件时强制Excel计算所有公式。这可以使用wb.setForceFormulaRecalculation(true);来完成。然后Excel计算公式，因此结果是正确的

复制问题的完整示例：

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.formula.BaseFormulaEvaluator;

import java.util.GregorianCalendar;

class CreateExcelFormulaWEEKNUM {

 public static void main(String[] args) throws Exception {

  try (
       //Workbook wb  = WorkbookFactory.create(new FileInputStream("./ExcelIn.xls")); FileOutputStream fileout = new FileOutputStream("./ExcelOut.xls");
       Workbook wb  = WorkbookFactory.create(new FileInputStream("./ExcelIn.xlsx")); FileOutputStream fileout = new FileOutputStream("./ExcelOut.xlsx");
        ) {

   Sheet sheet = wb.getSheetAt(0);

   Row row = sheet.getRow(0); if (row == null) row = sheet.createRow(0);
   Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0);
   cell.setCellValue(new GregorianCalendar(2021, 8, 27));
   CellReference cellReference = new CellReference(cell);
   
   Cell formCell = row.getCell(1); if(formCell == null) formCell = row.createCell(1);
   formCell.setCellFormula("WEEKNUM(" + cellReference.formatAsString() + ")"); // FormulaEvaluator evaluates to #VALUE because of [return_type] is not set
   //formCell.setCellFormula("WEEKNUM(" + cellReference.formatAsString() + ", 1)"); // FormulaEvaluator evaluates to 39 which is wrong as Excel evaluates to 40
   
   FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
   CellValue cellValue = formulaEvaluator.evaluate(formCell);
   System.out.println(cellValue);
   
   BaseFormulaEvaluator.evaluateAllFormulaCells(wb);
  
   wb.setForceFormulaRecalculation(true);

   wb.write(fileout);
  }

 }
}